
/* Drop Triggers */

DROP TRIGGER TRI_adminCheck_adminCheck;
DROP TRIGGER TRI_admin_adminCheck;
DROP TRIGGER TRI_babyAllDay_babyDayId;
DROP TRIGGER TRI_babyEat_id;
DROP TRIGGER TRI_babyInfo_infoNum;
DROP TRIGGER TRI_babyPlay_id;
DROP TRIGGER TRI_babySleep_id;
DROP TRIGGER TRI_baby_babyNum;
DROP TRIGGER TRI_boardComment_commentNum;
DROP TRIGGER TRI_board_boardNum;
DROP TRIGGER TRI_calendar_id;
DROP TRIGGER TRI_comment_commentNum;
DROP TRIGGER TRI_friends_friend_id;
DROP TRIGGER TRI_information_id;
DROP TRIGGER TRI_photoComment_photoCommentNum;
DROP TRIGGER TRI_photo_id;
DROP TRIGGER TRI_saleBoardComment_saleCommentNum;
DROP TRIGGER TRI_saleBoard_saleBoardNum;
DROP TRIGGER TRI_updates_id;
DROP TRIGGER TRI_update_id;
DROP TRIGGER TRI_vaccination_id;



/* Drop Tables */

DROP TABLE adminCheck CASCADE CONSTRAINTS;
DROP TABLE babyInfo CASCADE CONSTRAINTS;
DROP TABLE babyPlay CASCADE CONSTRAINTS;
DROP TABLE babyEat CASCADE CONSTRAINTS;
DROP TABLE babySleep CASCADE CONSTRAINTS;
DROP TABLE babyAllDay CASCADE CONSTRAINTS;
DROP TABLE baby CASCADE CONSTRAINTS;
DROP TABLE boardComment CASCADE CONSTRAINTS;
DROP TABLE board CASCADE CONSTRAINTS;
DROP TABLE calendar CASCADE CONSTRAINTS;
DROP TABLE friends CASCADE CONSTRAINTS;
DROP TABLE information CASCADE CONSTRAINTS;
DROP TABLE saleBoardComment CASCADE CONSTRAINTS;
DROP TABLE saleBoard CASCADE CONSTRAINTS;
DROP TABLE photoComment CASCADE CONSTRAINTS;
DROP TABLE photo CASCADE CONSTRAINTS;
DROP TABLE updates CASCADE CONSTRAINTS;
DROP TABLE mammaMember CASCADE CONSTRAINTS;
DROP TABLE vaccination CASCADE CONSTRAINTS;



/* Drop Sequences */

DROP SEQUENCE SEQ_adminCheck_adminCheck;
DROP SEQUENCE SEQ_admin_adminCheck;
DROP SEQUENCE SEQ_babyAllDay_babyDayId;
DROP SEQUENCE SEQ_babyEat_id;
DROP SEQUENCE SEQ_babyInfo_infoNum;
DROP SEQUENCE SEQ_babyPlay_id;
DROP SEQUENCE SEQ_babySleep_id;
DROP SEQUENCE SEQ_baby_babyNum;
DROP SEQUENCE SEQ_boardComment_commentNum;
DROP SEQUENCE SEQ_board_boardNum;
DROP SEQUENCE SEQ_calendar_id;
DROP SEQUENCE SEQ_comment_commentNum;
DROP SEQUENCE SEQ_friends_friend_id;
DROP SEQUENCE SEQ_information_id;
DROP SEQUENCE SEQ_photoComment_photoCommentNum;
DROP SEQUENCE SEQ_photo_id;
DROP SEQUENCE SEQ_saleBoardComment_saleCommentNum;
DROP SEQUENCE SEQ_saleBoard_saleBoardNum;
DROP SEQUENCE SEQ_updates_id;
DROP SEQUENCE SEQ_update_id;
DROP SEQUENCE SEQ_vaccination_id;




/* Create Sequences */

CREATE SEQUENCE SEQ_adminCheck_adminCheck INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_admin_adminCheck INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_babyAllDay_babyDayId INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_babyEat_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_babyInfo_infoNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_babyPlay_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_babySleep_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_baby_babyNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_boardComment_commentNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_board_boardNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_calendar_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_comment_commentNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_friends_friend_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_information_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_photoComment_photoCommentNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_photo_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_saleBoardComment_saleCommentNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_saleBoard_saleBoardNum INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_updates_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_update_id INCREMENT BY 1 START WITH 1;
CREATE SEQUENCE SEQ_vaccination_id INCREMENT BY 1 START WITH 1;



/* Create Tables */

CREATE TABLE adminCheck
(
	adminCheck number NOT NULL,
	email varchar2(25) NOT NULL,
	PRIMARY KEY (adminCheck)
);


CREATE TABLE baby
(
	babyNum number NOT NULL,
	name varchar2(18) NOT NULL,
	gender char DEFAULT '남' NOT NULL,
	bornDate date DEFAULT SYSDATE NOT NULL,
	memberEmail varchar2(25) NOT NULL,
	PRIMARY KEY (babyNum)
);


CREATE TABLE babyAllDay
(
	babyDayId number NOT NULL,
	inputdate date DEFAULT SYSDATE NOT NULL,
	startDate date,
	endDate date,
	babyNum number NOT NULL,
	PRIMARY KEY (babyDayId)
);


CREATE TABLE babyEat
(
	id number NOT NULL,
	content varchar2(3000),
	volume number,
	excrement varchar2(20) DEFAULT 'NULL',
	urine varchar2(20) DEFAULT 'NULL',
	babyDayId number NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE babyInfo
(
	infoNum number NOT NULL,
	height number(10,4),
	weight number(10,4),
	headSize number(10,4),
	inputdate date,
	babyNum number NOT NULL,
	PRIMARY KEY (infoNum)
);


CREATE TABLE babyPlay
(
	id number NOT NULL,
	play varchar2(600) DEFAULT 'NULL',
	babyDayId number NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE babySleep
(
	id number NOT NULL,
	volume number,
	degrees varchar2(1000),
	babyDayId number NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE board
(
	boardNum number NOT NULL,
	state varchar2(20),
	title varchar2(300),
	nickname varchar2(20),
	content varchar2(3000),
	viewcount number(1000),
	inputdate date DEFAULT SYSDATE,
	email varchar2(25) NOT NULL,
	PRIMARY KEY (boardNum)
);


CREATE TABLE boardComment
(
	commentNum number NOT NULL,
	nickname varchar2(20),
	content varchar2(3000) NOT NULL,
	inputdate date,
	email varchar2(25) NOT NULL,
	boardNum number NOT NULL,
	savedFile varchar2(100),
	PRIMARY KEY (commentNum)
);


CREATE TABLE calendar
(
	id number NOT NULL,
	toDo varchar2(300) NOT NULL,
	startDate date NOT NULL,
	endDate date,
	-- 높을수록 중요도가 높음
	importance number DEFAULT 1 NOT NULL,
	email varchar2(25) NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE friends
(
	friend_id number NOT NULL,
	user01 varchar2(25) NOT NULL,
	user02 varchar2(25) NOT NULL,
	-- 0: Pending Friend Request
	-- 1: Confirm Friend Request
	-- 2: You
	status varchar2(1) DEFAULT '0',
	inputdate date DEFAULT SYSDATE NOT NULL,
	PRIMARY KEY (friend_id, user01, user02)
);


CREATE TABLE information
(
	id number NOT NULL,
	infoName varchar2(100) NOT NULL,
	infoContent varchar2(3000) NOT NULL,
	content varchar2(1000),
	startDate date NOT NULL,
	endDate date,
	adminId varchar2(25) NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE mammaMember
(
	email varchar2(25) NOT NULL,
	name varchar2(12) NOT NULL,
	nickname varchar2(20) NOT NULL,
	password varchar2(16) NOT NULL,
	hasBaby char DEFAULT 'N' NOT NULL,
	inputdate date DEFAULT SYSDATE NOT NULL,
	hometown varchar2(20) NOT NULL,
	lastAccess date DEFAULT SYSDATE NOT NULL,
	message varchar2(300) DEFAULT ' ',
	profile_id number,
	PRIMARY KEY (email)
);


CREATE TABLE photo
(
	id number NOT NULL,
	content varchar2(1000),
	nickname varchar2(20) NOT NULL,
	inputdate date DEFAULT SYSDATE NOT NULL,
	-- 사진의 종류
	kind varchar2(100) DEFAULT 'default' NOT NULL,
	likeit number DEFAULT 0 NOT NULL,
	savedFile varchar2(100),
	originalFile varchar2(200),
	email varchar2(25) NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE photoComment
(
	photoCommentNum number NOT NULL,
	content varchar2(1000) NOT NULL,
	nickname varchar2(20) NOT NULL,
	likeit number DEFAULT 0 NOT NULL,
	id number NOT NULL,
	email varchar2(25) NOT NULL,
	PRIMARY KEY (photoCommentNum)
);


CREATE TABLE saleBoard
(
	saleBoardNum number NOT NULL,
	state varchar2(20),
	title varchar2(300),
	nickname varchar2(20),
	content varchar2(3000),
	inputdate date DEFAULT SYSDATE,
	email varchar2(25) NOT NULL,
	PRIMARY KEY (saleBoardNum)
);


CREATE TABLE saleBoardComment
(
	saleCommentNum number NOT NULL,
	nickname varchar2(20) NOT NULL,
	content varchar2(1000) NOT NULL,
	inputdate date NOT NULL,
	email varchar2(25) NOT NULL,
	saleBoardNum number NOT NULL,
	PRIMARY KEY (saleCommentNum)
);


CREATE TABLE updates
(
	id number NOT NULL,
	content varchar2(1000) NOT NULL,
	inputdate date DEFAULT SYSDATE,
	-- 0: not checked
	-- 1: checked
	status varchar2(1) DEFAULT '0' NOT NULL,
	email varchar2(25) NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE vaccination
(
	id number NOT NULL,
	title varchar2(300) NOT NULL,
	content varchar2(1000),
	startDate date NOT NULL,
	endMonth number NOT NULL,
	PRIMARY KEY (id)
);



/* Create Foreign Keys */

ALTER TABLE babyInfo
	ADD FOREIGN KEY (babyNum)
	REFERENCES baby (babyNum)
;


ALTER TABLE babyAllDay
	ADD FOREIGN KEY (babyNum)
	REFERENCES baby (babyNum)
;


ALTER TABLE babyPlay
	ADD FOREIGN KEY (babyDayId)
	REFERENCES babyAllDay (babyDayId)
;


ALTER TABLE babyEat
	ADD FOREIGN KEY (babyDayId)
	REFERENCES babyAllDay (babyDayId)
;


ALTER TABLE babySleep
	ADD FOREIGN KEY (babyDayId)
	REFERENCES babyAllDay (babyDayId)
;


ALTER TABLE boardComment
	ADD FOREIGN KEY (boardNum)
	REFERENCES board (boardNum)
;


ALTER TABLE saleBoard
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE boardComment
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE saleBoardComment
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE friends
	ADD FOREIGN KEY (user02)
	REFERENCES mammaMember (email)
;


ALTER TABLE photo
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE updates
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE photoComment
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE baby
	ADD FOREIGN KEY (memberEmail)
	REFERENCES mammaMember (email)
;


ALTER TABLE adminCheck
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE calendar
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE board
	ADD FOREIGN KEY (email)
	REFERENCES mammaMember (email)
;


ALTER TABLE friends
	ADD FOREIGN KEY (user01)
	REFERENCES mammaMember (email)
;


ALTER TABLE photoComment
	ADD FOREIGN KEY (id)
	REFERENCES photo (id)
;


ALTER TABLE saleBoardComment
	ADD FOREIGN KEY (saleBoardNum)
	REFERENCES saleBoard (saleBoardNum)
;



/* Create Triggers */

CREATE OR REPLACE TRIGGER TRI_adminCheck_adminCheck BEFORE INSERT ON adminCheck
FOR EACH ROW
BEGIN
	SELECT SEQ_adminCheck_adminCheck.nextval
	INTO :new.adminCheck
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_admin_adminCheck BEFORE INSERT ON admin
FOR EACH ROW
BEGIN
	SELECT SEQ_admin_adminCheck.nextval
	INTO :new.adminCheck
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_babyAllDay_babyDayId BEFORE INSERT ON babyAllDay
FOR EACH ROW
BEGIN
	SELECT SEQ_babyAllDay_babyDayId.nextval
	INTO :new.babyDayId
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_babyEat_id BEFORE INSERT ON babyEat
FOR EACH ROW
BEGIN
	SELECT SEQ_babyEat_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_babyInfo_infoNum BEFORE INSERT ON babyInfo
FOR EACH ROW
BEGIN
	SELECT SEQ_babyInfo_infoNum.nextval
	INTO :new.infoNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_babyPlay_id BEFORE INSERT ON babyPlay
FOR EACH ROW
BEGIN
	SELECT SEQ_babyPlay_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_babySleep_id BEFORE INSERT ON babySleep
FOR EACH ROW
BEGIN
	SELECT SEQ_babySleep_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_baby_babyNum BEFORE INSERT ON baby
FOR EACH ROW
BEGIN
	SELECT SEQ_baby_babyNum.nextval
	INTO :new.babyNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_boardComment_commentNum BEFORE INSERT ON boardComment
FOR EACH ROW
BEGIN
	SELECT SEQ_boardComment_commentNum.nextval
	INTO :new.commentNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_board_boardNum BEFORE INSERT ON board
FOR EACH ROW
BEGIN
	SELECT SEQ_board_boardNum.nextval
	INTO :new.boardNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_calendar_id BEFORE INSERT ON calendar
FOR EACH ROW
BEGIN
	SELECT SEQ_calendar_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_comment_commentNum BEFORE INSERT ON comment
FOR EACH ROW
BEGIN
	SELECT SEQ_comment_commentNum.nextval
	INTO :new.commentNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_friends_friend_id BEFORE INSERT ON friends
FOR EACH ROW
BEGIN
	SELECT SEQ_friends_friend_id.nextval
	INTO :new.friend_id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_information_id BEFORE INSERT ON information
FOR EACH ROW
BEGIN
	SELECT SEQ_information_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_photoComment_photoCommentNum BEFORE INSERT ON photoComment
FOR EACH ROW
BEGIN
	SELECT SEQ_photoComment_photoCommentNum.nextval
	INTO :new.photoCommentNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_photo_id BEFORE INSERT ON photo
FOR EACH ROW
BEGIN
	SELECT SEQ_photo_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_saleBoardComment_saleCommentNum BEFORE INSERT ON saleBoardComment
FOR EACH ROW
BEGIN
	SELECT SEQ_saleBoardComment_saleCommentNum.nextval
	INTO :new.saleCommentNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_saleBoard_saleBoardNum BEFORE INSERT ON saleBoard
FOR EACH ROW
BEGIN
	SELECT SEQ_saleBoard_saleBoardNum.nextval
	INTO :new.saleBoardNum
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_updates_id BEFORE INSERT ON updates
FOR EACH ROW
BEGIN
	SELECT SEQ_updates_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_update_id BEFORE INSERT ON update
FOR EACH ROW
BEGIN
	SELECT SEQ_update_id.nextval
	INTO :new.id
	FROM dual;
END;

/

CREATE OR REPLACE TRIGGER TRI_vaccination_id BEFORE INSERT ON vaccination
FOR EACH ROW
BEGIN
	SELECT SEQ_vaccination_id.nextval
	INTO :new.id
	FROM dual;
END;

/




/* Comments */

COMMENT ON COLUMN calendar.importance IS '높을수록 중요도가 높음';
COMMENT ON COLUMN friends.status IS '0: Pending Friend Request
1: Confirm Friend Request
2: You';
COMMENT ON COLUMN photo.kind IS '사진의 종류';
COMMENT ON COLUMN updates.status IS '0: not checked
1: checked';



